// 控制器层，进行业务逻辑处理，都是用逻辑sql语句获得数据，供给其他文件函数调用
// 文章相关业务逻辑

// 引入数据库执行的工具函数
import { executeSql } from '../util/db.js'

// 查询文章总数
export const selectArticlesCount = function(){
    let sql = "select count(id) count from articles where isDelete = 0";
    return executeSql(sql);
}

// 随机返回5条文章
export const selectArticlesRandom = function(start=0){    
    // 确保start是非负整数
    let startInt = Math.max(0, parseInt(start));
    let sql = `select id,cover,title from articles where isDelete = 0 limit ${startInt},5`;
    return executeSql(sql);
} 

// 获取热门文章(按阅读量排序，7条)  
export const selectHotArticles = function(limit = 7){
    let limitInt = parseInt(limit) || 7;
    let sql = `select a.id, a.title, a.cover, a.date, a.author, a.\`read\`, a.categoryId,
                      SUBSTRING(a.content, 1, 200) as content, t.name as type,
                      count(c.id) as comments
               from articles a 
               left join categories t on a.categoryId = t.id
               left join comments c on a.id = c.articleId and c.state = '已通过'
               where a.isDelete = 0
               group by a.id 
               order by a.\`read\` desc 
               limit ${limitInt}`;
    return executeSql(sql);
}

// 获取热门文章内容摘要（专门用于焦点关注接口）
export const selectHotArticlesContent = async function(limit = 7){
    let limitInt = parseInt(limit) || 7;
    // 先获取热门文章ID
    let sql = `select id from articles where isDelete = 0 order by \`read\` desc limit ${limitInt}`;
    let articles = await executeSql(sql);
    
    // 为每篇文章获取内容摘要
    let contentPromises = articles.map(async (article) => {
        let contentSql = `select SUBSTRING(content, 1, 500) as contentPreview from articles where id = ?`;
        let contentResult = await executeSql(contentSql, [article.id]);
        return {
            id: article.id,
            contentPreview: contentResult[0]?.contentPreview || ""
        };
    });
    
    return Promise.all(contentPromises);
}

// 获取最新文章列表 (5条)
export const selectLatestArticles = function(page = 1, pageSize = 5){
    let pageInt = parseInt(page) || 1;
    let pageSizeInt = parseInt(pageSize) || 5;
    let offset = (pageInt - 1) * pageSizeInt;
    let sql = `select a.id, a.title, a.cover, a.date, a.author, a.\`read\`, a.categoryId, 
                      SUBSTRING(a.content, 1, 200) as content, t.name as type,
                      count(c.id) as comments
               from articles a 
               left join categories t on a.categoryId = t.id
               left join comments c on a.id = c.articleId and c.state = '已通过'
               where a.isDelete = 0
               group by a.id 
               order by a.date desc 
               limit ${offset}, ${pageSizeInt}`;
    return executeSql(sql);
}

// 获取最新文章内容摘要（专门用于最新资讯接口）
export const selectLatestArticlesContent = async function(page = 1, pageSize = 5){
    let pageInt = parseInt(page) || 1;
    let pageSizeInt = parseInt(pageSize) || 5;
    let offset = (pageInt - 1) * pageSizeInt;
    
    // 获取最新文章信息并连接评论表获取评论数
    let sql = `select a.id, a.title, a.cover, a.date, a.author, a.\`read\`, a.categoryId, 
                      count(c.id) as comments
               from articles a 
               left join comments c on a.id = c.articleId and c.state = '已通过'
               where a.isDelete = 0
               group by a.id 
               order by a.date desc 
               limit ${offset}, ${pageSizeInt}`;
    let articles = await executeSql(sql);
    
    // 为每篇文章获取内容摘要
    let contentPromises = articles.map(async (article) => {
        let contentSql = `select SUBSTRING(content, 1, 200) as contentPreview from articles where id = ?`;
        let contentResult = await executeSql(contentSql, [article.id]);
        return {
            ...article,
            contentPreview: contentResult[0]?.contentPreview || ""
        };
    });
    
    return Promise.all(contentPromises);
}

// 根据ID获取文章详情
export const selectArticleById = function(id){
   let sql = `select a.*,t.name as type,count(c.id) as comments 
   from articles a left join categories t on a.categoryId = t.id
                   left join comments c on a.id = c.articleId and c.state = '已通过'
   where a.isDelete = 0 and a.id = ?
   group by a.id`;
   return executeSql(sql, [id]);
}

// 增加文章阅读量
export const updateArticleRead = function(id){
    let sql = `update articles set \`read\` = \`read\` + 1 where id = ?`;
    return executeSql(sql, [parseInt(id)]);
}

//------------------文章搜索----------------------
// 根据分类获取文章列表
export const selectArticlesByCategory = function(categoryId, page = 1, pageSize = 10){
    let pageInt = parseInt(page) || 1;
    let pageSizeInt = parseInt(pageSize) || 10;
    let offset = (pageInt - 1) * pageSizeInt;
    let sql = `select id,title,cover,date,author,\`read\`,categoryId from articles where isDelete = 0 and categoryId = ? order by date desc limit ${offset},${pageSizeInt}`;
    return executeSql(sql, [parseInt(categoryId)]);
}

// 搜索文章 (搜索标题，智能提取intro，并连接评论表获取评论数，连接分类表获取分类名称，支持分类筛选)
export const searchArticles = async function(keyword, categoryId, page = 1, pageSize = 10){
    // 参数验证和处理
    if (!keyword || keyword.trim() === '') {
        keyword = ''; // 空字符串会匹配所有内容
    }
    
    // 确保参数是整数
    let pageInt = parseInt(page) || 1;
    let pageSizeInt = parseInt(pageSize) || 10;
    let offset = (pageInt - 1) * pageSizeInt;
    
    let searchPattern = `%${keyword}%`;
    let params = [searchPattern];
    
    // 构建WHERE条件
    let whereCondition = `a.isDelete = 0 and a.title like ?`;
    
    // 如果有分类筛选
    if (categoryId && parseInt(categoryId) > 0) {
        whereCondition += ` and a.categoryId = ?`;
        params.push(parseInt(categoryId));
    }
    
    // 查询文章信息并连接评论表获取评论数，连接分类表获取分类名称，获取完整content用于智能提取intro
    let sql = `select a.id, a.title, a.cover, a.date, a.author, a.\`read\`, a.categoryId, 
                      a.content, count(c.id) as comments, t.name as categoryName
               from articles a 
               left join comments c on a.id = c.articleId and c.state = '已通过'
               left join categories t on a.categoryId = t.id
               where ${whereCondition}
               group by a.id 
               order by a.date desc 
               limit ${offset},${pageSizeInt}`;
    
    let result = await executeSql(sql, params);
    
    // 在应用层处理intro内容（与advancedSearchArticles使用相同的智能提取逻辑）
    return result.map(item => {
        let intro = '';
        
        // 始终从content中智能提取intro
        let content = item.content || '';
        if (content.length > 0) {
            // 去除HTML标签
            content = content.replace(/<[^>]*>/g, ' ');
            // 去除HTML实体
            content = content.replace(/&[a-zA-Z0-9#]+;/g, ' ');
            // 去除多余空白字符
            content = content.replace(/\s+/g, ' ').trim();
            
            // 特别处理：对于类型1的文章，使用更宽松的提取策略
            if (item.categoryId == 1) {
                // 对于游戏类文章，直接截取更多内容，不过度筛选
                let cleanedContent = content;
                
                // 查找第一个完整句子的结束位置
                let sentenceEnd = -1;
                let searchLen = Math.min(cleanedContent.length, 300);
                for (let i = 50; i < searchLen; i++) {
                    if ('。！？.!?'.includes(cleanedContent[i])) {
                        sentenceEnd = i;
                        break;
                    }
                }
                
                if (sentenceEnd > 50) {
                    intro = cleanedContent.substring(0, sentenceEnd + 1);
                } else {
                    // 直接截取前200个字符
                    intro = cleanedContent.substring(0, 200);
                }
            } else {
                // 其他类型文章使用原来的智能提取逻辑
                let sentences = content.split(/[。！？.!?]/);
                let meaningfulContent = '';
                
                for (let sentence of sentences) {
                    sentence = sentence.trim();
                    // 跳过日期格式和太短的句子
                    if (sentence.length >= 15 && 
                        !/^\d+年\d+月\d+日/.test(sentence) && 
                        !/^\d+月\d+日/.test(sentence) &&
                        !/^[\d\s,，、]*$/.test(sentence)) {
                        meaningfulContent += sentence + '。';
                        if (meaningfulContent.length >= 150) {
                            break;
                        }
                    }
                }
                
                // 如果智能提取失败，使用原始内容但跳过开头
                if (meaningfulContent.length < 60) {
                    // 查找第一个句号后的内容
                    let firstPeriodIndex = content.indexOf('。');
                    if (firstPeriodIndex > 0 && firstPeriodIndex < content.length - 20) {
                        intro = content.substring(firstPeriodIndex + 1).trim();
                    } else {
                        // 跳过前40个字符
                        intro = content.length > 40 ? content.substring(40) : content;
                    }
                } else {
                    intro = meaningfulContent;
                }
            }
        }
        
        // 如果intro仍然为空或太短，使用标题作为备用
        if (!intro || intro.length < 30) {
            intro = item.title ? `${item.title} - 点击查看精彩内容详情` : '文章内容摘要暂无...';
        }
        
        // 截取250个字符
        if (intro.length > 250) {
            intro = intro.substring(0, 250) + '...';
        }
        
        return {
            ...item,
            intro: intro,
            content: undefined // 不返回完整content，节省传输
        };
    });
}

// 搜索文章总数 (支持关键字和分类筛选)
export const searchArticlesCount = function(keyword, categoryId){
    // 参数验证和处理
    if (!keyword || keyword.trim() === '') {
        keyword = ''; // 空字符串会匹配所有内容
    }
    
    let searchPattern = `%${keyword}%`;
    let params = [searchPattern];
    
    // 构建WHERE条件
    let whereCondition = `isDelete = 0 and title like ?`;
    
    // 如果有分类筛选
    if (categoryId && parseInt(categoryId) > 0) {
        whereCondition += ` and categoryId = ?`;
        params.push(parseInt(categoryId));
    }
    
    let sql = `select count(id) count from articles where ${whereCondition}`;
    
    return executeSql(sql, params);
}

// 高级搜索文章 (支持关键字、分类、状态的组合搜索)
export const advancedSearchArticles = async function(options){
    const { keyword = '', categoryId, state, page = 1, pageSize = 10 } = options;
    
    // 确保参数是整数
    let pageInt = parseInt(page) || 1;
    let pageSizeInt = parseInt(pageSize) || 10;
    let offset = (pageInt - 1) * pageSizeInt;
    
    let params = [];
    let whereConditions = ['a.isDelete = 0'];
    
    // 关键字搜索
    if (keyword && keyword.trim() !== '') {
        whereConditions.push('a.title like ?');
        params.push(`%${keyword}%`);
    }
    
    // 分类筛选
    if (categoryId && parseInt(categoryId) > 0) {
        whereConditions.push('a.categoryId = ?');
        params.push(parseInt(categoryId));
    }
    
    // 状态筛选
    if (state && state.trim() !== '') {
        whereConditions.push('a.state = ?');
        params.push(state);
    }
    
    let whereClause = whereConditions.join(' and ');
    
    // 查询文章信息并连接评论表获取评论数，连接分类表获取分类名称
    // 先获取基础数据，intro处理在应用层完成
    let sql = `select a.id, a.title, a.cover, a.date, a.author, a.\`read\`, a.categoryId, a.state,
                      a.content, count(c.id) as comments, t.name as type
               from articles a 
               left join comments c on a.id = c.articleId and c.state = '已通过'
               left join categories t on a.categoryId = t.id
               where ${whereClause}
               group by a.id 
               order by a.date desc 
               limit ${offset}, ${pageSizeInt}`;
    
    let result = await executeSql(sql, params);
    
    // 在应用层处理intro内容
    return result.map(item => {
        let intro = '';
        
        // 始终从content中智能提取intro，忽略数据库的intro字段
        let content = item.content || '';
        if (content.length > 0) {
            // 去除HTML标签
            content = content.replace(/<[^>]*>/g, ' ');
            // 去除HTML实体
            content = content.replace(/&[a-zA-Z0-9#]+;/g, ' ');
            // 去除多余空白字符
            content = content.replace(/\s+/g, ' ').trim();
            
            // 特别处理：对于类型1的文章，使用更宽松的提取策略
            if (item.categoryId == 1) {
                // 对于游戏类文章，直接截取更多内容，不过度筛选
                let cleanedContent = content;
                
                // 查找第一个完整句子的结束位置
                let sentenceEnd = -1;
                let searchLen = Math.min(cleanedContent.length, 300);
                for (let i = 50; i < searchLen; i++) {
                    if ('。！？.!?'.includes(cleanedContent[i])) {
                        sentenceEnd = i;
                        break;
                    }
                }
                
                if (sentenceEnd > 50) {
                    intro = cleanedContent.substring(0, sentenceEnd + 1);
                } else {
                    // 直接截取前200个字符
                    intro = cleanedContent.substring(0, 200);
                }
            } else {
                // 其他类型文章使用原来的智能提取逻辑
                let sentences = content.split(/[。！？.!?]/);
                let meaningfulContent = '';
                
                for (let sentence of sentences) {
                    sentence = sentence.trim();
                    // 跳过日期格式和太短的句子
                    if (sentence.length >= 15 && 
                        !/^\d+年\d+月\d+日/.test(sentence) && 
                        !/^\d+月\d+日/.test(sentence) &&
                        !/^[\d\s,，、]*$/.test(sentence)) {
                        meaningfulContent += sentence + '。';
                        if (meaningfulContent.length >= 150) {
                            break;
                        }
                    }
                }
                
                // 如果智能提取失败，使用原始内容但跳过开头
                if (meaningfulContent.length < 60) {
                    // 查找第一个句号后的内容
                    let firstPeriodIndex = content.indexOf('。');
                    if (firstPeriodIndex > 0 && firstPeriodIndex < content.length - 20) {
                        intro = content.substring(firstPeriodIndex + 1).trim();
                    } else {
                        // 跳过前40个字符
                        intro = content.length > 40 ? content.substring(40) : content;
                    }
                } else {
                    intro = meaningfulContent;
                }
            }
        }
        
        // 如果intro仍然为空或太短，使用标题作为备用
        if (!intro || intro.length < 30) {
            intro = item.title ? `${item.title} - 点击查看精彩内容详情` : '文章内容摘要暂无...';
        }
        
        // 截取250个字符
        if (intro.length > 250) {
            intro = intro.substring(0, 250) + '...';
        }
        
        return {
            ...item,
            intro: intro,
            content: undefined // 不返回完整content，节省传输
        };
    });
}

// 高级搜索文章总数
export const advancedSearchArticlesCount = function(options){
    const { keyword = '', categoryId, state } = options;
    
    let params = [];
    let whereConditions = ['isDelete = 0'];
    
    // 关键字搜索
    if (keyword && keyword.trim() !== '') {
        whereConditions.push('title like ?');
        params.push(`%${keyword}%`);
    }
    
    // 分类筛选
    if (categoryId && parseInt(categoryId) > 0) {
        whereConditions.push('categoryId = ?');
        params.push(parseInt(categoryId));
    }
    
    // 状态筛选
    if (state && state.trim() !== '') {
        whereConditions.push('state = ?');
        params.push(state);
    }
    
    let whereClause = whereConditions.join(' and ');
    let sql = `select count(id) count from articles where ${whereClause}`;
    
    return executeSql(sql, params);
}

// 获取上一篇文章
export const getPrevArticle = function(currentId){
    let sql = `select id, title from articles where id < ? and isDelete = 0 order by id desc limit 1`;
    return executeSql(sql, [parseInt(currentId)]);
}

// 获取下一篇文章  
export const getNextArticle = function(currentId){
    let sql = `select id, title from articles where id > ? and isDelete = 0 order by id asc limit 1`;
    return executeSql(sql, [parseInt(currentId)]);
}

//------------------管理后台相关 - CRUD操作----------------------
// 添加文章
export const insertArticle = function(title, content, author, categoryId, cover = null, state = '已发布'){
    let now = new Date();
    let date = now.toISOString().split('T')[0]; 
    let sql = `insert into articles (title, content, cover, author, categoryId, date, state, isDelete) values (?, ?, ?, ?, ?, ?, ?, 0)`;
    return executeSql(sql, [title, content, cover, author, parseInt(categoryId), date, state]);
}

// 更新文章
export const updateArticle = function(id, title, content, author, categoryId, cover = '/uploads/default-cover.jpg'){
    let sql = `update articles set title = ?, content = ?, cover = ?, author = ?, categoryId = ? where id = ? and isDelete = 0`;
    return executeSql(sql, [title, content, cover, author, parseInt(categoryId), parseInt(id)]);
}

// 删除文章（软删除）
export const deleteArticle = function(id){
    let sql = `update articles set isDelete = 1 where id = ?`;
    return executeSql(sql, [parseInt(id)]);
}

//------------------管理后台相关 - 统计功能----------------------
// 获取今日文章数量
export const selectTodayArticlesCount = function(today){
    let sql = `select count(id) count from articles where date = ? and isDelete = 0`;
    return executeSql(sql, [today]);
}

// 按日期获取文章数量
export const selectArticlesByDate = function(date){
    let sql = `select count(id) count from articles where date = ? and isDelete = 0`;
    return executeSql(sql, [date]);
}
